package com.mzsoft.ai.service;

import com.alibaba.fastjson.JSON;
import com.mzsoft.ai.utils.DBUtils;
import com.mzsoft.ai.utils.DateUtils;
import com.mzsoft.ai.utils.PrimaryKeyUtil;
import com.mzsoft.ai.utils.file.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 插入表操作
 */
public class OperateDataService {

    private static Logger logger = LoggerFactory.getLogger(OperateDataService.class);

    // 定义连接所需的常量
    private static String dialect = ""; //数据库方言
    private static String userName = ""; // Oracle数据库的用户名
    private static String password = ""; // Oracle数据库的用户密码
    private static String driver = "";  // "com.microsoft.sqlserver.jdbc.SQLServerDriver";//"oracle.jdbc.OracleDriver"; // 驱动
    private static String url = ""; //"jdbc:sqlserver://172.28.100.47:1433;DatabaseName=v3x";//"jdbc:oracle:thin:@172.28.100.116:1525:HSEUAT";

    static {
        try {
            String userDir = System.getProperty("user.dir");
            String configPath = userDir+"/配置导入配置文件/config.json";
            logger.info("userDir:"+userDir);
            if(!new File(configPath).exists()){
                throw new RuntimeException("配置服务器文件【"+configPath+"】不存在!");
            }
            String jdbcServer = FileUtils.ReadFile(configPath);
            logger.debug("appServerMapStr:"+jdbcServer);
            Map<String,Object> appServerMap = JSON.parseObject(jdbcServer, Map.class);
            logger.debug("appServerMap:"+JSON.toJSONString(appServerMap));
            userName = (String) appServerMap.get("userName");
            password = (String) appServerMap.get("password");
            url = (String) appServerMap.get("url");
            driver = (String) appServerMap.get("driver");
            dialect = (String) appServerMap.get("dialect");
        }catch (Exception e){
            logger.error(e.getMessage(),e);
        }

    }

    public static void insertData(String folderPath,List<String> sqlList) throws SQLException, IOException {

        logger.debug("insert plan data start.......");

        DBUtils dbUtils = new DBUtils(userName,password,url,driver);
        boolean connectFlag = dbUtils.checkDBConnect();

        String basePath = System.getProperty("user.dir");
        if(connectFlag){

            for(String sqlFilePath : sqlList){
                sqlFilePath = basePath + folderPath +"/sql/"+ sqlFilePath;
                String sql = FileUtils.ReadFile(sqlFilePath);
                logger.debug("sql:"+sql);
                dbUtils.executeUpdate(sql);
            }
        }

        logger.debug("insert plan data end.......");
    }

    public static void operateMultiData(String businessCode, Map<String, Object> multiSheetFieldMap, Map<String, Object> resultMap) throws SQLException {

        logger.debug("query plan data start.......");
        DBUtils dbUtils = new DBUtils(userName,password,url,driver);
        boolean connectFlag = dbUtils.checkDBConnect();

        if(connectFlag){

            for(String sheetNo : multiSheetFieldMap.keySet()){
                String sql = "CREATE TABLE "+businessCode+"_"+sheetNo+" ( ";

                Map<String,Object> sheetMap = (Map<String, Object>) multiSheetFieldMap.get(sheetNo);

                String saveType = (String) sheetMap.get("saveType");
                Map<String,Object> fieldMap = (Map<String, Object>) sheetMap.get("column");

                if("row".equals(saveType)){
                    if("mysql".equals(dialect)){
                        sql += "fieldCode varchar(500),fieldValue varchar(500),";
                    }else if("oracle".equals(dialect)){
                        sql += "fieldCode varchar2(500),fieldValue varchar2(500),";
                    }
                }else {
                    for(String key : fieldMap.keySet()){
                        sql += fieldMap.get(key);
                        if("mysql".equals(dialect)){
                            sql += " varchar(500),";
                        }else if("oracle".equals(dialect)){
                            sql += " varchar2(500),";
                        }
                    }
                }

                if("mysql".equals(dialect)){
                    sql += "create_date varchar(100),sid varchar(100),businessId varchar(100)";
                    sql += ") ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;";
                }else if("oracle".equals(dialect)){
                    sql += "create_date varchar2(100),sid varchar2(100),businessId varchar2(100))";
                }

                logger.debug("sql:"+ sql);
                dbUtils.executeUpdate(sql);
            }
        }
        logger.debug("query plan data end.......");

        for(String sheetNo : resultMap.keySet()){
            //sheet单个读取data返回结果
            List<Map<String, Object>> sheetList = (List<Map<String, Object>>) resultMap.get(sheetNo);
            //sheet单个fieldMap
            Map<String,Object> sheetMap = (Map<String, Object>) multiSheetFieldMap.get(sheetNo);

            //isImpl 该sheet是否需要导入 true:是，false:否,如果是不配置默认为 true
            boolean isImpl = sheetMap.get("isImpl") != null ? (boolean) sheetMap.get("isImpl") : true;
            String saveType = (String) sheetMap.get("saveType");
            Map<String,Object> fieldMap = (Map<String, Object>) sheetMap.get("column");

            //如果是不需要导入则跳过
            if(!isImpl){
                continue;
            }

            if(StringUtils.isNotBlank(saveType) && "row".equals(saveType)){
                for (Map<String,Object> map : sheetList){

                    String sid = PrimaryKeyUtil.getPrimaryKey();
                    String sql = "insert into "+businessCode+"_"+sheetNo+" (";
                    //拼接字段
                    sql += "fieldCode,fieldValue,create_date,sid) values (?,?,?,?)";
                    List<Map<String,Object>> paramList = new ArrayList<>();
                    for(String key : map.keySet()) {
                        Map<String,Object> params = new LinkedHashMap<>();
                        params.put("1",key);
                        params.put("2",map.get(key));
                        if ("mysql".equals(dialect)) {
                            params.put("3",DateUtils.getCurrentDate(DateUtils.FORMAT7));
                            params.put("4",sid);
                        } else if ("oracle".equals(dialect)) {
                            params.put("3",DateUtils.getCurrentDate(DateUtils.FORMAT7));
                            params.put("4",sid);
                        }
                        paramList.add(params);

                    }
                    logger.debug("insert sql:" + sql);
                    dbUtils.executeBatchOperate(sql,paramList);
                }
            }else {
                List<Map<String,Object>> paramList = new ArrayList<>();
                String sql = "";
                for (Map<String,Object> map : sheetList) {
                    sql = "insert into " + businessCode + "_" + sheetNo + " (";
                    //拼接字段
                    for (String key : fieldMap.keySet()) {
                        sql += fieldMap.get(key) + ",";
                    }

                    sql += "create_date,sid ) values (";

                    Map<String, Object> params = new LinkedHashMap<>();

                    //拼接值
                    for (String key : fieldMap.keySet()) {
                        sql += "?,";
                        params.put(key, map.get(fieldMap.get(key)));
                    }

                    sql += "?,?)";
                    if ("mysql".equals(dialect)) {
                        params.put("currDate", DateUtils.getCurrentDate(DateUtils.FORMAT7));
                        params.put("keyId", PrimaryKeyUtil.getPrimaryKey());
                    } else if ("oracle".equals(dialect)) {
                        params.put("currDate", DateUtils.getCurrentDate(DateUtils.FORMAT7));
                        params.put("keyId", PrimaryKeyUtil.getPrimaryKey());
                    }
                    paramList.add(params);

                }
                logger.debug("insert sql:" + sql);
                dbUtils.executeBatchOperate(sql, paramList);
            }
        }
    }

    public static void fallBackData(String folderPath, List<String> sqlList) throws SQLException, IOException {

        logger.debug("fallBack data start.......");

        DBUtils dbUtils = new DBUtils(userName,password,url,driver);
        boolean connectFlag = dbUtils.checkDBConnect();

        String basePath = System.getProperty("user.dir");
        if(connectFlag){

            for(String sqlFilePath : sqlList){
                sqlFilePath = basePath + folderPath +"/fallback/"+ sqlFilePath;
                String sql = FileUtils.ReadFile(sqlFilePath);
                logger.debug("sql:"+sql);
                dbUtils.executeUpdate(sql);
            }
        }

        logger.debug("fallBack data end.......");
    }
}
